﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 日铁
{
    public class DBHelper
    {
        //从app.Config中读取连接字符串
        //(1)先引用System.Configuration程序集
        //(2)导入System.Configuration命名空间
        private static readonly string connString = ConfigurationManager.ConnectionStrings["RT"].ConnectionString;

        /// <summary>
        /// 创建Connection对象
        /// </summary>
        /// <returns>Connnection对象</returns>
        public static SqlConnection CreateConnection()
        {
            SqlConnection connection = new SqlConnection(connString);
            return connection;
        }


        /// <summary>
        /// 创建Command对象
        /// </summary>

        /// <returns>Command对象</returns>

        private static SqlCommand CreateCommand(string commadText, SqlConnection conn, bool isProdecure, params SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = commadText;
            command.Connection = conn;
            if (isProdecure)
                command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command;
        }

        /// <summary>
        /// 执行SQL语句或调用存储过程，并返回第一行第一列的值，忽略其它的行和列
        /// </summary>
        /// <param name="commadText">要执行的SQL语句</param>
        /// <param name="conn">要基于的Connection对象</param>
        /// <param name="isProdecure">是否是存储过程，true：存储过程,false:sql语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>第一行第一列的值</returns>
        public static Object ExecuteScalar(string commadText, SqlConnection conn, bool isProdecure, params SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = CreateCommand(commadText, conn, isProdecure, parameters);
                Open(conn);
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行SQL语句或调用存储过程，并返回受影响的行数
        /// </summary>
        /// <param name="commadText">要执行的SQL语句</param>
        /// <param name="conn">要基于的Connection对象</param>
        /// <param name="isProdecure">是否是存储过程，true：存储过程,false:sql语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string commadText, SqlConnection conn, bool isProdecure, params SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = CreateCommand(commadText, conn, isProdecure, parameters);
                Open(conn);
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行SQL语句或调用存储过程，并返回SqlDataReader对象
        /// </summary>
        /// <param name="commadText">要执行的SQL语句</param>
        /// <param name="conn">要基于的Connection对象</param>
        /// <param name="isProdecure">是否是存储过程，true：存储过程,false:sql语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string commadText, SqlConnection conn, bool isProdecure, params SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = CreateCommand(commadText, conn, isProdecure, parameters);
                Open(conn);
                return command.ExecuteReader();
            }
            catch (Exception ex) { throw ex; }
        }

        /// <summary>
        /// 执行SQL语句或调用存储过程，并返回DataTable
        /// </summary>
        /// <param name="commadText">要执行的SQL语句</param>
        /// <param name="conn">要基于的Connection对象</param>
        /// <param name="isProdecure">是否是存储过程，true：存储过程,false:sql语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string commadText, SqlConnection conn, bool isProdecure, params SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = CreateCommand(commadText, conn, isProdecure, parameters);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="conn">连接对象</param>
        private static void Open(SqlConnection conn)
        {
            if (conn.State != System.Data.ConnectionState.Open)
                //打开连接
                conn.Open();
        }
    }
}
